# Date and Time Units

The `INTERVAL` keyword can be used to add or subtract a time interval of time to a [DATETIME](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime), [DATE](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/date) or [TIME](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/time) value.

The syntax is:

```sql
INTERVAL time_quantity time_unit
```

For example, the `SECOND` unit is used below by the [DATE\_ADD()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/date_add) function:

```sql
SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
+-------------------------------------------+
| '2008-12-31 23:59:59' + INTERVAL 1 SECOND |
+-------------------------------------------+
| 2009-01-01 00:00:00                       |
+-------------------------------------------+
```

The following units are valid:

| Unit                | Description                             |
| ------------------- | --------------------------------------- |
| MICROSECOND         | Microseconds                            |
| SECOND              | Seconds                                 |
| MINUTE              | Minutes                                 |
| HOUR                | Hours                                   |
| DAY                 | Days                                    |
| WEEK                | Weeks                                   |
| MONTH               | Months                                  |
| QUARTER             | Quarters                                |
| YEAR                | Years                                   |
| SECOND\_MICROSECOND | Seconds.Microseconds                    |
| MINUTE\_MICROSECOND | Minutes.Seconds.Microseconds            |
| MINUTE\_SECOND      | Minutes.Seconds                         |
| HOUR\_MICROSECOND   | Hours.Minutes.Seconds.Microseconds      |
| HOUR\_SECOND        | Hours.Minutes.Seconds                   |
| HOUR\_MINUTE        | Hours.Minutes                           |
| DAY\_MICROSECOND    | Days Hours.Minutes.Seconds.Microseconds |
| DAY\_SECOND         | Days Hours.Minutes.Seconds              |
| DAY\_MINUTE         | Days Hours.Minutes                      |
| DAY\_HOUR           | Days Hours                              |
| YEAR\_MONTH         | Years-Months                            |

The time units containing an underscore are composite; that is, they consist of multiple base time units. For base time units, `time_quantity` is an integer number. For composite units, the quantity must be expressed as a string with multiple integer numbers separated by any punctuation character.

Example of composite units:

```sql
INTERVAL '2:2' YEAR_MONTH
INTERVAL '1:30:30' HOUR_SECOND
INTERVAL '1!30!30' HOUR_SECOND -- same as above
```

Time units can be used in the following contexts:

* after a [+](https://mariadb.com/docs/server/reference/sql-structure/operators/arithmetic-operators/addition-operator) or a [-](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/set-operations/minus) operator;
* with the following `DATE` or `TIME` functions: [ADDDATE()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/adddate), [SUBDATE()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/subdate), [DATE\_ADD()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/date_add), [DATE\_SUB()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/date_sub), [TIMESTAMPADD()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/timestampadd), [TIMESTAMPDIFF()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/timestampdiff), [EXTRACT()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/extract);
* in the `ON SCHEDULE` clause of [CREATE EVENT](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-event) and [ALTER EVENT](https://mariadb.com/docs/server/server-usage/triggers-events/event-scheduler/alter-event);
* when defining a [partitioning](https://mariadb.com/docs/server/server-usage/tables/create-table#partitions) `BY SYSTEM_TIME` .

## See Also

* [Date and time literals](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/date-and-time-literals)
* [Operator Precedence](https://mariadb.com/docs/server/reference/sql-structure/operators/operator-precedence)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}